﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;

namespace OALPTool
{
    public class ExportExcelColor
    {

        #region 导出Excel

        /// <summary>
        /// 将所见的内容导出为excel
        /// </summary>

        private object objApp_Late;
        private object objBook_Late;
        private object objBooks_Late;
        private object objSheets_Late;
        private object objSheet_Late;
        private object objRange_Late;
        private object[] Parameters;

        private int treeDepth;
        // private string title; 
        // private List<string> header; 
        // private List<string> footer; 
        private bool autoFit = true;

        private TreeView _ColHeaderTreeView;
        private int iNodeLevels;
        private DataGridView _DataGridView;



        private int myGetNodeLevels(TreeNodeCollection tnc)
        {
            if (tnc == null) return 0;

            foreach (TreeNode tn in tnc)
            {
                if ((tn.Level + 1) > iNodeLevels)//tn.Level是從0開始的
                {
                    iNodeLevels = tn.Level + 1;
                }

                if (tn.Nodes.Count > 0)
                {
                    myGetNodeLevels(tn.Nodes);
                }

            }

            return iNodeLevels;
        }
        /// <summary>
        /// 通过_DataGridView导出到Excel工作表。 如果标题是真实的，网格标题将出现在第1行。 数据将在第2行开始。
        /// </summary>
        /// <param name="ColDataGridView"></param>
        /// <param name="ColHeaderTreeView"></param>
        /// <param name="captions"></param>
        /// <param name="fast"></param>
        public void Export2Excel(DataGridView ColDataGridView, TreeView ColHeaderTreeView, bool captions)
        {
            _DataGridView = ColDataGridView;
            _ColHeaderTreeView = ColHeaderTreeView;
            if (_ColHeaderTreeView != null)
            {
                if (iNodeLevels == 0)
                {
                    iNodeLevels = 1;//初始為1
                    int iNodeDeep = myGetNodeLevels(_ColHeaderTreeView.Nodes);
                    treeDepth = iNodeDeep; //2013-04-11LDJ
                }

            }
            int kk = 0;
            foreach (DataGridViewColumn col in _DataGridView.Columns)
            {
                if (col.GetType().Name == "DataGridViewTextBoxColumn" && col.Visible == true)// 
                {
                    kk++;
                }
            }
            // visibleCols = kk;
            string[] headers = new string[kk];
            string[] columns = new string[kk];
            string[] colName = new string[kk];

            int i = 0;
            int c = 0;
            int m = 0;

            for (c = 0; c < _DataGridView.Columns.Count; c++)
            {
                for (int j = 0; j < _DataGridView.Columns.Count; j++)
                {
                    DataGridViewColumn tmpcol = _DataGridView.Columns[j];
                    if (tmpcol.DisplayIndex == c)
                    {
                        if (tmpcol.GetType().Name == "DataGridViewTextBoxColumn" && tmpcol.Visible) //不显示的隐藏列初始化为tag＝0 
                        {
                            headers[c - m] = tmpcol.HeaderText;
                            i = c - m + 1;
                            columns[c - m] = ConvertColumnNum2String(i);
                            colName[c - m] = tmpcol.Name;
                        }
                        else
                        {
                            m++;
                        }
                        break;
                    }
                }
            }

            try
            {
                // Get the class type and instantiate Excel.
                Type objClassType;
                objClassType = Type.GetTypeFromProgID("Excel.Application");
                objApp_Late = Activator.CreateInstance(objClassType);
                //Get the workbooks collection.
                objBooks_Late = objApp_Late.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, objApp_Late, null);
                //Add a new workbook.
                objBook_Late = objBooks_Late.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, objBooks_Late, null);
                //Get the worksheets collection.
                objSheets_Late = objBook_Late.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, objBook_Late, null);
                //Get the first worksheet.
                Parameters = new Object[1];
                Parameters[0] = 1;
                objSheet_Late = objSheets_Late.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, objSheets_Late, Parameters);

                if (captions)
                {
                    //这里重新写入excel的头
                    //多维表头
                    //_ColHeaderTreeView  表头TreeView
                    //iNodeLevels 表头的层数
                    //_ColHeaderTreeView.Nodes.a
                    if (this._ColHeaderTreeView.Nodes.Count > 0)
                    {
                        TreeView tr = new TreeView();
                        CopyTree(_ColHeaderTreeView.Nodes, tr.Nodes);
                        WriteCell(tr.Nodes, 1, 1);
                    }
                    else
                    {
                        // Create the headers in the first row of the sheet
                        for (c = 0; c < kk; c++)
                        {
                            //Get a range object that contains cell.
                            Parameters = new Object[2];
                            Parameters[0] = columns[c] + "1";
                            Parameters[1] = Missing.Value;
                            objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                            //Write Headers in cell.
                            Parameters = new Object[1];
                            Parameters[0] = headers[c];
                            objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters);
                        }
                    }
                }

                // Now add the data from the grid to the sheet starting in row 2

                int startRow = 2;
                if (iNodeLevels != 0)
                {
                    startRow = iNodeLevels + 1;
                }

                //使用2维数组进行粘贴，速度更快
                string[,] vals = new string[_DataGridView.Rows.Count, headers.Length];
                string startCell = columns[0] + Convert.ToString(startRow);
                string endCell = columns[columns.Length - 1] + Convert.ToString(startRow + _DataGridView.Rows.Count - 1);

                //TODO: writh to excel
                for (i = 0; i < _DataGridView.RowCount; i++)
                {
                    int j = 0;

                    if (i < _DataGridView.NewRowIndex || _DataGridView.NewRowIndex == -1)
                    #region data
                    {
                        foreach (string txtCol in colName)
                        {
                            DataGridViewColumn col = _DataGridView.Columns[txtCol];
                            if (col.Visible)
                            {
                                if (_DataGridView.Rows[i].Cells[col.Name].Value != null)
                                {
                                    vals[i, j] = _DataGridView.Rows[i].Cells[col.Name].FormattedValue.ToString();//.Replace(" 0:00:00", ""); //string.Format("{0:d}",dt);//2005-11-5
                                }
                                else
                                {
                                    vals[i, j] = "";
                                }
                                j++;
                            }
                        }
                    }
                    #endregion data
                    //else if (i == _DataGridView.NewRowIndex && computeColumns.Count > 0)
                    //#region sum footer
                    //{
                    //    Dictionary<string, string> dict = dictComputeColumns();
                    //    foreach (string txtCol in colName)
                    //    {
                    //        string v;
                    //        if (dict.TryGetValue(txtCol, out v))
                    //        {
                    //            vals[i, j] = v;
                    //        }
                    //        j++;
                    //    }
                    //}
                    //#endregion sum footer
                }

                //块写入
                Parameters = new Object[2];
                Parameters[0] = startCell;
                Parameters[1] = endCell;
                objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                //设为文本格式
                object[] o = new object[1];
                o[0] = "@";
                objRange_Late.GetType().InvokeMember("NumberFormatLocal", BindingFlags.SetProperty, null, objRange_Late, o);
                //写入值
                Parameters = new Object[1];
                Parameters[0] = vals;
                objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters);

                //左右合并单元格
                mergeRowsCell(startRow);

                ////上下合并单元格
                ////start row = startRow, end row= startRow+ startRow+_DataGridView.RowCount
                ////calculate column index tobe bombined
                //int endRow = startRow + _DataGridView.RowCount - 1;
                //if (_mergecolumnname != null && _mergecolumnname.Count > 0)
                //{
                //    foreach (string cn in _mergecolumnname)
                //    {
                //        int col = Array.IndexOf<string>(colName, cn) + 1;
                //        if (col > 0) mergeColumnsCell(startRow, endRow, col);//设置的合并列没有显示的不进行合并操作
                //    }
                //}
                drawLine();
                int maxRows = iNodeLevels + 1 + _DataGridView.RowCount;
                if (iNodeLevels == 0) maxRows++;

                setAlignment(colName, columns, maxRows); //设置导出数据的对齐方式

                //// 写入footer
                //if (footer != null)
                //{
                //    writeFooter(maxRows);
                //}

                ////写入header
                //if (header != null)
                //{
                //    writeHeader();
                //}

                //if (title != null)
                //{
                //    writeTitle();
                //}

                //Return control of Excel to the user.
                Parameters = new Object[1];
                Parameters[0] = true;
                objApp_Late.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, objApp_Late, Parameters);
                objApp_Late.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, objApp_Late, Parameters);
            }
            catch (Exception theException)
            {

                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }

        }

        /// <summary>
        /// 数字转换为Excel字母数字的列
        /// </summary>
        /// <param name="columnNum">数字</param>
        /// <returns>返回字符串</returns>
        private string ConvertColumnNum2String(int columnNum)
        {
            if (columnNum > 26)
            {
                return string.Format("{0}{1}", (char)(((columnNum - 1) / 26) + 64), (char)(((columnNum - 1) % 26) + 65));
            }
            else
            {
                return ((char)(columnNum + 64)).ToString();
            }
        }

        //复制tree，将隐藏的节点去除
        private void CopyTree(TreeNodeCollection nodes, TreeNodeCollection tgNodes)
        {
            foreach (TreeNode nd in nodes)
            {
                if (nd.Tag == null || nd.Tag.ToString() != "hide")
                {
                    TreeNode newNode = new TreeNode(nd.Text);
                    tgNodes.Add(newNode);
                    if (nd.Nodes.Count > 0) CopyTree(nd.Nodes, newNode.Nodes);
                }
            }
        }

        /// <summary>
        /// 遍历treeview并写入
        /// </summary>
        /// <param name="rootNodes">treeview 节点集合</param>
        /// <param name="excelRow">写入的行:从1开始</param>
        /// <param name="excelColumn">写入的列:从1开始</param>
        /// <returns>占用的列数</returns>
        private int WriteCell(TreeNodeCollection rootNodes, int excelRow, int excelColumn)
        {
            int cellCount = 1;
            if (rootNodes.Count < 1)
            {
                WriteExcelROW(excelRow - 1, excelColumn, cellCount, treeDepth);//sd为treeview的深度
                return cellCount;
            }
            int cellCountSum = 0;
            foreach (TreeNode TNode in rootNodes)
            {
                cellCount = WriteCell(TNode.Nodes, excelRow + 1, excelColumn);
                WriteExcel(excelRow, excelColumn, cellCount, TNode.Text);
                cellCountSum += cellCount;
                excelColumn += cellCount;
            }
            return cellCountSum;
        }

        private void WriteExcelROW(int excelRow, int excelColumn, int cellCountSum, int sd)
        {
            //列数没有限制
            string point1 = ConvertColumnNum2String(excelColumn) + excelRow.ToString();//单元格起始点 如:A1 
            string point2 = ConvertColumnNum2String(excelColumn) + sd.ToString();//单元格起始点 如:A1 
            if (point1 != point2)
            {
                objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, new object[] { point1, point2 });
                objRange_Late.GetType().InvokeMember("MergeCells", BindingFlags.SetProperty, null, objRange_Late, new object[] { true });
            }

        }

        /// <summary>
        /// 报表header的输出
        /// </summary>
        /// <param name="excelRow"></param>
        /// <param name="excelColumn"></param>
        /// <param name="cellCountSum"></param>
        /// <param name="excelValue"></param>
        private void WriteExcel(int excelRow, int excelColumn, int cellCountSum, string excelValue)
        {
            //列数没有限制
            string point1 = ConvertColumnNum2String(excelColumn) + excelRow.ToString();//单元格起始点 如:A1
            string point2 = ConvertColumnNum2String(excelColumn + cellCountSum - 1) + excelRow.ToString();//单元格结束点 如:B4

            //_Range = _Worksheet.get_Range(point1, point2);//获取单元格
            objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, new object[] { point1, point2 });
            if (cellCountSum > 0)
            {
                //_Range.MergeCells = true; //合并单元格
                objRange_Late.GetType().InvokeMember("MergeCells", BindingFlags.SetProperty, null, objRange_Late, new object[] { true });
            }

            objRange_Late.GetType().InvokeMember("HorizontalAlignment", BindingFlags.SetProperty, null, objRange_Late, new object[] { -4108 });//内容水平居中 Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter 
            objRange_Late.GetType().InvokeMember("VerticalAlignment", BindingFlags.SetProperty, null, objRange_Late, new object[] { -4108 });  //内容垂直居中Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter 

            //_Worksheet.Cells[_Range.Row, _Range.Column] = excelValue;//把内容写入单元格
            Parameters = new Object[1];
            Parameters[0] = excelValue;
            objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters);
        }

        /// <summary>
        /// 写数据的内容画实线
        /// </summary>
        private void drawLine()
        {
            object Range = objSheet_Late.GetType().InvokeMember("UsedRange", BindingFlags.GetProperty, null, objSheet_Late, null);
            object[] args = new object[] { 1 };
            object Borders = Range.GetType().InvokeMember("Borders", BindingFlags.GetProperty, null, Range, null);
            Borders = Range.GetType().InvokeMember("LineStyle", BindingFlags.SetProperty, null, Borders, args);
            //自动适应行列的宽高
            if (autoFit)
            {
                Object dataColumns = Range.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, Range, null);
                dataColumns.GetType().InvokeMember("AutoFit", BindingFlags.InvokeMethod, null, dataColumns, null);
            }
        }

        //TODO:excel alignment
        /// <summary>
        /// 设置所有的数据对齐方式与显示的一致
        /// </summary>
        /// <param name="colName">列名数组</param>
        /// <param name="colName">Excel列名数组</param>
        /// <param name="maxRows">最大的行值</param>
        private void setAlignment(string[] colName, string[] columns, int maxRows)
        {
            //默认左对齐
            string point1;
            string point2;

            int startRow = 2;
            if (iNodeLevels > 1)
            {
                startRow = iNodeLevels + 1;
            }
            int i = 0;

            foreach (string col in colName)
            {
                point1 = columns[i] + startRow.ToString();//单元格起始点 如:A3
                point2 = columns[i] + maxRows.ToString();//单元格结束点 如:A15
                objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, new object[] { point1, point2 });
                objRange_Late.GetType().InvokeMember("VerticalAlignment", BindingFlags.SetProperty, null, objRange_Late, new object[] { -4108 });  //内容垂直居中Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter 
                if (_DataGridView.Columns[col].DefaultCellStyle.Alignment.ToString().Contains("Right"))
                {
                    objRange_Late.GetType().InvokeMember("HorizontalAlignment", BindingFlags.SetProperty, null, objRange_Late, new object[] { -4152 });//Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight 
                }
                else if (_DataGridView.Columns[col].DefaultCellStyle.Alignment.ToString().Contains("Center"))
                {
                    objRange_Late.GetType().InvokeMember("HorizontalAlignment", BindingFlags.SetProperty, null, objRange_Late, new object[] { -4108 });//内容水平居中 Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter 
                }
                //else if (_DataGridView.Columns[col].DefaultCellStyle.BackColor.)
                //{
                //}
                i++;
            }
        }

        /// <summary>
        /// 左右行合并
        /// </summary>
        /// <param name="startRow"></param>
        private void mergeRowsCell(int startRow)
        {
            //    reCalculateAllStock();
            //    objApp_Late.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, objApp_Late, new object[] { false });
            //    foreach (int i in stockOfAllRows.Keys)
            //    {
            //        foreach (string tempKeys in stockOfAllRows[i].Keys)
            //        {
            //            {
            //                string theValue = getValue(startRow + i, displayedColumns.IndexOf(tempKeys) + 1);
            //                string[] points = new string[2];

            //                int smallvalue = 10000, bigvalue = 0;
            //                if (stockOfAllRows[i][tempKeys].Count < 2) continue;
            //                for (int r = 0; r < stockOfAllRows[i][tempKeys].Count; r++)
            //                {
            //                    int tempvalue = displayedColumns.IndexOf(stockOfAllRows[i][tempKeys][r]) + 1;
            //                    if (smallvalue > tempvalue)
            //                        smallvalue = displayedColumns.IndexOf(stockOfAllRows[i][tempKeys][r]) + 1;
            //                    if (bigvalue < tempvalue)
            //                        bigvalue = tempvalue;
            //                }
            //                points[0] = ConvertColumnNum2String(smallvalue) + (startRow + i).ToString();
            //                points[1] = ConvertColumnNum2String(bigvalue) + (startRow + i).ToString();
            //                objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, points);
            //                objRange_Late.GetType().InvokeMember("Merge", BindingFlags.InvokeMethod, null, objRange_Late, null);

            //            }

            //        }
            //    }

        }

        #endregion


    }
}
